package com.queryBuilder.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBHelper {
	private static final String url = "jdbc:mysql://www.niubai.net.cn/test";
	private static final String name = "com.mysql.jdbc.Driver";
	private static final String user = "root";
	private static final String password = "root";
	private static final String sql = "insert into vistor_info (ipaddress,vis_time,area) values (?,?,?)";

	private static Connection conn = null;
	private static PreparedStatement pst = null;

	/**
	 * 初始化数据库
	 */
	public static void init() {
		try {
			Class.forName(name);// 指定连接类型
			conn = DriverManager.getConnection(url, user, password);// 获取连接
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 关闭连接
	 */
	private static void close() {
		try {
			conn.close();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 保存访问者信息
	 * 
	 * @param ip
	 * @param time
	 * @param area
	 * @throws SQLException
	 */
	public static void executeSQL(String ip, String time, String area)
			throws SQLException {
		if (conn == null)
			init();
		PreparedStatement pstmt = conn.prepareStatement(sql);

		pstmt.setString(1, ip);
		pstmt.setString(2, time);
		pstmt.setString(3, area);
		pstmt.execute();
		close();
	}

	/**
	 * 统计访问者个数
	 * 
	 * @return
	 */
	public static int countVistor() {
		int count = 0;
		if (conn == null)
			init();
		try {
			PreparedStatement pstmt = conn
					.prepareStatement("select count(*) from vistor_info");

			ResultSet rst = pstmt.executeQuery();
			while (rst.next()) {
				count = rst.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	public static List<Map<String, String>> detailIps() throws SQLException {
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		String sql1 = "SELECT VIS_TIME, COUNT(vis_time) FROM vistor_info GROUP BY vis_time";
		PreparedStatement pstmt = conn.prepareStatement(sql1);
		Map<String, String> map = null;
		ResultSet rst = pstmt.executeQuery();
		while (rst.next()) {
			map = new HashMap<String, String>();
			String date = rst.getString(1);
			int count = rst.getInt(2);
			map.put("date", date);
			map.put("count", String.valueOf(count));
			list.add(map);

		}
		close();

		return list;
	}
}
